package com.ncloud.chnl.mapper;


import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.data.repository.query.Param;

import com.ncloud.chnl.entity.Sale;

@Mapper
public interface SaleMapper {

	@Select(value = { "select * from sys_sales where user_id = #{userId}" })
	@Results({ @Result(column = "sale_id", property = "saleId"),
		@Result(column = "chnl_id", property = "chnlId"), @Result(column = "user_id", property = "userId"),
		@Result(column = "job_title", property = "jobTitle"), @Result(column = "remark", property = "remark") })
	public Sale getSale(Long userId);

	@Select(value = { "select * from sys_sales where sale_id = #{saleId}" })
	@Results({ @Result(column = "sale_id", property = "saleId"),
			@Result(column = "chnl_id", property = "chnlId"), @Result(column = "user_id", property = "userId"),
			@Result(column = "job_title", property = "jobTitle"), @Result(column = "remark", property = "remark") })
	public Sale getSaleBySaleId(Long saleId);

	@Select(value = {
			"select a.chnl_name from sys_chnl a,sys_sales b where a.chnl_id = b.chnl_id and b.user_id = #{userId}" })
	public String getChnlName(Long userId);

	@Insert(value = ("insert into sys_sales (sale_id,chnl_id,user_id,job_title,remark)"
			+ " values (#{saleId},#{chnlId},#{userId},#{jobTitle},#{remark});"))
	public int insertSale(Sale sale);

	@Update(value = ("update sys_sales set chnl_id = #{chnlId} ,user_id = #{userId} ,job_title = #{jobTitle} , "
			+ "remark = #{remark} where sale_id = #{saleId};"))
	public int updateSale(Sale sale);

	@Delete(value = ("delete from sys_sales where sale_id = #{saleId};"))
	public int deleteSale(Long saleId);

	@Select(value = { "select a.job_title,a.user_id,b.name,b.account,b.email,b.mobile,b.telephone,b.state,f_get_label('USER_STATE',b.state) as state_name,"
			+ "b.user_type,f_get_label('USER_TYPE',b.user_type) as user_type_name,b.remark from sys_sales a,sys_user b where a.chnl_id = #{chnlId} and a.user_id = b.id and b.state!='0'" })
	@Results({@Result(column = "name", property = "name"),@Result(column = "state_name", property = "stateName"),
			@Result(column = "user_id", property = "userId",javaType=String.class),@Result(column = "state", property = "state"),
			@Result(column = "job_title", property = "jobTitle"), @Result(column = "phone", property = "phone"),
			@Result(column = "user_type", property = "userType"), @Result(column = "user_type_name", property = "userTypeName"),
			@Result(column = "email", property = "email"), @Result(column = "mobile", property = "mobile"),
			@Result(column = "remark", property = "remark"), @Result(column = "telephone", property = "telephone")})
	public List<Map<String, Object>> getChnlSales(Long chnlId);
	
	@Select(value = { "select c.chnl_id as group_id,c.chnl_name as group_name,f_get_chnlName(c.chnl_id) as chnl_name,"
			+ " if(c.chnl_level='1',c.chnl_id,c.parent_id) as chnl_id, a.job_title,a.user_id,b.name,b.account,b.email,"
			+ " b.mobile,b.telephone,b.state,f_get_label('USER_STATE',b.state) as state_name,b.user_type,"
			+ " f_get_label('USER_TYPE',b.user_type) as user_type_name,b.remark from sys_sales a,sys_user b,sys_chnl c"
			+ " where FIND_IN_SET(a.chnl_id,f_getChnlList(#{userId})) and a.chnl_id != 1 and a.user_id = b.id and b.state!='0'"
			+ " and a.chnl_id=c.chnl_id and c.state='1'" })
	@Results({@Result(javaType=String.class,column = "group_id", property = "groupId"),@Result(column = "group_name", property = "groupName"),
			@Result(column = "chnl_name", property = "chnlName"),@Result(javaType=String.class,column = "chnl_id", property = "chnlId"),
			@Result(column = "name", property = "name"),@Result(column = "state_name", property = "stateName"),
			@Result(column = "user_id", property = "userId",javaType=String.class),@Result(column = "state", property = "state"),
			@Result(column = "job_title", property = "jobTitle"), @Result(column = "phone", property = "phone"),
			@Result(column = "user_type", property = "userType"), @Result(column = "user_type_name", property = "userTypeName"),
			@Result(column = "email", property = "email"), @Result(column = "mobile", property = "mobile"),
			@Result(column = "remark", property = "remark"),@Result(column = "telephone", property = "telephone")})
	public List<Map<String, Object>> getAllChnlSales(@Param("userId")Long userId);
}
